Electricity tariffs in Switzerland¶
ElCom, the Federal Electricity Commission, collects data on the electricity tariffs for household and companies. This data is published as Linked Data.
In this tutorial, we will show how to work with Linked Data. Mainly, we will see how to work with data on electricity tariffs.
We will look into how to query, process, and visualize it.
1. Setup
1.1 SPARQL endpoints
1.2 SPARQL client
2. Data
2.1 Electricity tariffs
2.2 Municipalities
3.1 Energy prices across regions
3.2 Energy prices for small and big enterprises
3.3 Energy prices for small and big households
3.4 Competition between energy providers
3.5 Grid costs and population density
3.6 Free grid usage
3.7 Free energy
3.8 Free aidfee
3.9 How many people benefit from fee waivers?
3.10 Which companies benefit from fee waivers?
Setup¶
SPARQL endpoints¶
For electricity tariffs¶
Data on all electricity tariffs is published as Linked Data. It can be accessed with SPARQL queries.
You can send queries using HTTP requests. The API endpoint is https://lindas.admin.ch/query/.
For geodata¶
Different municipalities may have different tariffs. To understand their location, we will work with Swiss geodata. It is published as Linked Data. It can be accessed using API endpoint under https://ld.geo.admin.ch/query.
import json
import string
import folium
import mapclassify
import matplotlib as mpl
import matplotlib.pyplot as plt
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from graphly.api_client import SparqlClient
%matplotlib inline
# Uncomment to install dependencies in Colab environment
#!pip install mapclassify
#!pip install git+https://github.com/zazuko/graphly.git
sparql = SparqlClient("https://lindas.admin.ch/query")
geosparql = SparqlClient("https://ld.geo.admin.ch/query")
sparql.add_prefixes({
"schema": "<http://schema.org/>",
"cube": "<https://cube.link/>",
"elcom": "<https://energy.ld.admin.ch/elcom/electricityprice/dimension/>",
"admin": "<https://schema.ld.admin.ch/>"
})
geosparql.add_prefixes({
"dct": "<http://purl.org/dc/terms/>",
"geonames": "<http://www.geonames.org/ontology#>",
"schema": "<http://schema.org/>",
"geosparql": "<http://www.opengis.net/ont/geosparql#>",
})
SPARQL queries can become very long. To improve the readibility, we will work wih prefixes.
Using the add_prefixes method, we can define persistent prefixes.
Every time you send a query, graphly will now automatically add the prefixes for you.
Data¶
Our goal is to understand the varaiability in electricity prices. To do so, we will work with electricity tariffs, and municipality data.
Electricity tariffs¶
Electricity prices are composed of:
- Grid fee
Price for transporting electricity from the power plant to the home. The revenue is used to finance the maintenance and expansion of the electricity grid, for example overhead lines, pylons and transformers.
- Energy price
Price for the electrical energy supplied. The grid operator either generates this energy with its own power plants or buys it from upstream suppliers. The energy price is also influenced by the type of energy source. A high share of renewable energy (e.g. wind, solar, biomass energy) usually leads to higher energy prices.
- Municipality taxes
Municipal and cantonal taxes and fees. These include, for example, concession fees or local political energy levies.
- Aidfee
Federal tax for the promotion of renewable energies, and the protection of waters and fish. The amount of the aidfee is set annually by the Federal Council. The levy is the same everywhere.
These components vary across:
- time
- products
- consumption categories
- energy providers
- municipalities
The place where you live, the consumption category to which you belong, the energy provider you chose, and the product you subscribed for will affect your monthly bills.
Let's take a look at these differences. To simplify the results, we will work with data from 2020. We will also focus on a single standard product, which is available across all of Switzerland.
We can query the tariffs for different municipalities and categories as follows (heads up, the query may take a while to execute!):
query = """
SELECT ?municipality_id ?category ?energy ?grid ?aidfee (?community_fees + ?aidfee as ?taxes) ?fixcosts ?variablecosts
FROM <https://lindas.admin.ch/elcom/electricityprice>
WHERE {
<https://energy.ld.admin.ch/elcom/electricityprice/observation/> cube:observation ?observation.
?observation
elcom:category/schema:name ?category;
elcom:municipality ?municipality_id;
elcom:period "2020"^^<http://www.w3.org/2001/XMLSchema#gYear>;
elcom:product <https://energy.ld.admin.ch/elcom/electricityprice/product/standard>;
elcom:fixcosts ?fixcosts;
elcom:total ?variablecosts;
elcom:gridusage ?grid;
elcom:energy ?energy;
elcom:charge ?community_fees;
elcom:aidfee ?aidfee.
}
ORDER BY ?muncipality ?category ?variablecosts
"""
tariffs = sparql.send_query(query)
tariffs = tariffs.groupby(["municipality_id", "category"]).first().reset_index()
tariffs.head()
Great! We can analyse all price components across municipalities and consumption categories. But what do these consumption categories stand for?
Let's query all categories and their description.
query = """
SELECT DISTINCT ?category ?description
WHERE {
GRAPH <https://lindas.admin.ch/elcom/electricityprice> {
?s <https://energy.ld.admin.ch/elcom/electricityprice/dimension/category> ?category_uri.
?category_uri schema:name ?category .
?category_uri schema:description ?description .
}
}
ORDER BY ?category
"""
df = sparql.send_query(query)
df
So we have 15 different categories. Those knowning german will notice that the categories cover househoulds and companies. The difference is in expected energy consumption.
The tariffs themselves are not very meaningful. "How much is my monthly bill?" is much more relatable question. Let's try to answer that.
def extract_consumption(description: str) -> int:
"""
Extract average electricity consumption from a description.
Args:
description: Category description for electricity tariffs
Returns:
int: Electricity consumption in kWh/year
"""
number_as_string = description.split(" kWh/Jahr")[0]
return int(number_as_string.translate(str.maketrans('', '', string.punctuation)))
cat2description = dict(zip(df.category, df.description))
cat2consumption = dict(zip(df.category, [extract_consumption(d) for d in df.description]))
tariffs["consumption"] = tariffs[["category"]].replace({"category": cat2consumption})
tariffs["monthly_bill"] = ((tariffs.consumption*tariffs.variablecosts/12 + tariffs.fixcosts)/100).round(2)
tariffs.head()
We can now use the tariffs table to analyze regional differences in energy prices.
Municipalities¶
Electricity prices vary across municipalities. The tariff table gives us an overview of prices at each municipality. But what are their names? Where are they located? And how many people live there?
This information can be obtained using the SPARQL Endpoind from Swisstopo, Switzerland's national mapping agency.
We can query the list of municipalities, alongside with its boundaries as follows:
query = """
SELECT ?municipality_id ?municipality ?population ?boundary
WHERE {
?muni_iri dct:hasVersion ?version ;
geonames:featureCode geonames:A.ADM3 .
?version schema:validUntil "2020-12-31"^^<http://www.w3.org/2001/XMLSchema#date>;
geonames:population ?population;
schema:name ?municipality;
geosparql:hasGeometry/geosparql:asWKT ?boundary.
BIND(IRI(REPLACE(STR(?muni_iri), "https://ld.geo.admin.ch/boundaries/", "https://ld.admin.ch/")) AS ?municipality_id)
}
"""
communes = geosparql.send_query(query)
communes = communes.set_crs(epsg=4326)
communes.head()
You may notice that we defined a corrdinate reference system. The default projection for this endpoint is EPSG:4326, also known as WGS84.
SparqlClient automatically returns a geopandas datafrme. By defining the coordinate reference system, we can start using its geoanalytics capacities immediately!
Let's start by visually inspecting all the communes.
communes.plot()
Great! Now we have a geospatial representation of all swiss municipalities and we also have energy tariffs across the municipalities.
Let's join this information in one dataframe.
join = pd.merge(communes[["municipality_id", "municipality", "population", "boundary"]], tariffs, how="inner", on="municipality_id")
join.drop(columns=["variablecosts", "consumption"], inplace=True)
join.head()
Analysis¶
Now that we know what is in the data, let's dive deeper into it.
Energy prices across regions¶
We have seen the prices vary across regions. But how big are these differences? Let's take a look.
We will start with defining a plot_tariffs function. It will classify prices into buckets, and show the differences on the map.
style_function = lambda x: {'fillColor': '#ffffff',
'color':'#000000',
'fillOpacity': 0.1,
'weight': 0.1}
highlight_function = lambda x: {'fillColor': '#989898',
'color':'#000000',
'fillOpacity': 0.8}
def plot_tariffs(df, variable, variable_description, title):
classifier = mapclassify.NaturalBreaks(y=df[variable], k=5)
bins = [df[variable].min()] + list(classifier.bins)
m = folium.Map(location=[46.83, 8.13], zoom_start=8, tiles="cartodbpositron")
folium.Choropleth(
geo_data=json.loads(df.to_json()),
data=df,
columns=["municipality", variable],
key_on="feature.properties.municipality",
fill_color="YlGn",
fill_opacity=1,
line_weight=0,
smooth_factor=0,
bins=bins,
reset=True,
legend_name=variable_description,
).add_to(m)
hover = folium.features.GeoJson(
df,
style_function=style_function,
control=False,
highlight_function=highlight_function,
tooltip=folium.features.GeoJsonTooltip(
fields=['municipality', variable],
aliases=['Municipality: ', variable_description + ": "],
style=("background-color: white; color: #333333; font-family: arial; font-size: 12px; padding: 10px;")
)
)
folium.LayerControl().add_to(m)
m.add_child(hover)
m.keep_in_front(hover)
title_html = '''<h3 align="center" style="font-size:16px"><b>{}</b></h3>'''.format(title)
m.get_root().html.add_child(folium.Element(title_html))
return m
Now let's use it! How do the prices vary for an average household?
category = "H2"
plot_df = join[join.category == category]
plot_tariffs(plot_df, "monthly_bill", "Average monthly bill [CHF]", cat2description[category])
What about a small company?
category = "C2"
plot_df = join[join.category == "C2"]
plot_tariffs(plot_df, "monthly_bill", "Average monthly bill [CHF]", cat2description[category])